<font size="+3"><strong>Databases: PyMongo</strong></font>

Databases: PyMongo

# Working with PyMongo

Working with PyMongo¶

For all of these examples, we're going to be working with the "lagos" collection in the "air-quality" database. Before we can do anything else, we need to bring in pandas (which we won't use until the very end), pprint (a module that lets us see the data in an understandable way), and PyMongo (a library for working with MongoDB databases).

## Databases

Databases¶

Data comes to us in lots of different ways, and one of those ways is in a database. A database is a collection of data.

## Servers and Clients

Servers and Clients¶

Next, we need to connect to a server. A database server is where the database resides; it can be accessed using a client. Without a client, a database is just a collection of information that we can't work with, because we have no way in. We're going to be learning more about a database called MongoDB, and we'll use PrettyPrinter to make the information it generates easier to understand. Here's how the connection works:

## Semi-structured Data

Semi-structured Data¶

Databases are designed to work with either structured data or semi-structured data. In this part of the course, we're going to be working with databases that contain semi-structured data. Data is semi-structured when it has some kind of organizing logic, but that logic can't be displayed using rows and columns. Your email account contains semi-structured data if it’s divided into sections like Inbox, Sent, and Trash. If you’ve ever seen tweets from Twitter grouped by hashtag, that’s semi-structured data too. Semi-structured data is also used in sensor readings, which is what we'll be working with here.

## Exploring a Database

Exploring a Database¶

So, now that we're connected to a server, let's take a look at what's there. Working our way down the specificity scale, the first thing we need to do is figure out which databases are on this server. To see which databases on the server, we'll use the list_databases method, like this:

It looks like this server contains four databases: `"admin"`, `"air-quality"`, `"config"`, and `"local"`. We're only interested in `"air-quality"`, so let's connect to that one:

It looks like this server contains four databases: "admin", "air-quality", "config", and "local". We're only interested in "air-quality", so let's connect to that one:

In MongoDB, a **database** is a container for **collections**. Each database gets its own set of files, and a single MongoDB **server** typically has multiple databases.

In MongoDB, a database is a container for collections. Each database gets its own set of files, and a single MongoDB server typically has multiple databases.

## Collections

Collections¶

Let's use a for loop to take a look at the collections in the "air-quality" database:

As you can see, there are three actual collections here: `"nairobi"`, `"lagos"`, and `"dar-es-salaam"`. Since we're only interested in the `"lagos"` collection, let's get it on its own like this: 

As you can see, there are three actual collections here: "nairobi", "lagos", and "dar-es-salaam". Since we're only interested in the "lagos" collection, let's get it on its own like this:

## Documents

Documents¶

A MongoDB **document** is an individual record of data in a **collection**, and is the basic unit of analysis in MongoDB. Documents come with **metadata** that helps us understand what the document is; we'll get back to that in a minute. In the meantime, let's use the [`count_documents`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.count_documents) method to see how many documents the `"lagos"` collection contains:

A MongoDB document is an individual record of data in a collection, and is the basic unit of analysis in MongoDB. Documents come with metadata that helps us understand what the document is; we'll get back to that in a minute. In the meantime, let's use the count_documents method to see how many documents the "lagos" collection contains:

<font size="+1">Practice</font>

Practice

Try it yourself! Bring in all the necessary libraries and modules, then connect to the "air-quality" database and print the number of documents in the "nairobi" collection.

### Retrieving Data

Retrieving Data¶

Now that we know how many documents the "lagos" collection contains, let's take a closer look at what's there. The first thing you'll notice is that the output starts out with a curly bracket ({), and ends with a curly bracket (}). That tells us that this information is a dictionary. To access documents in the collection, we'll use two methods: find and find_one. As you might expect, find will retrieve all the documents, and find_one will bring back only the first document. For now, let's stick to find_one; we'll come back to find later.

Just like everywhere else, we'll need to assign a variable name to whatever comes back, so let's call this one result.

### Key-Value Pairs

Key-Value Pairs¶

There's a lot going on here! Let's work from the bottom up, starting with this:

{
    'temperature': 27.0,
    'timestamp': datetime.datetime(2017, 9, 6, 13, 18, 10, 120000)
}

The actual data is labeled temperature and timestamp, and if seeing it presented this way seems familiar, that's because what you're seeing at the bottom are two key-value pairs. In PyMongo, "_id" is always the primary key. Primary keys are the column(s) which contain values that uniquely identify each row in a table; we'll talk about that more in a minute.

### Metadata

Metadata¶

Next, we have this:

'metadata': { 'lat': 6.602,
              'lon': 3.351,
              'measurement': 'temperature',
              'sensor_id': 9,
              'sensor_type': 'DHT11',
              'site': 2}

This is the document's metadata. Metadata is data about the data. If you’re working with a database, its data is the information it contains, and its metadata describes what that information is. In MongoDB, each document often has metadata of its own. If we go back to the example of your email account, each message in your Sent folder includes both the message itself and information about when you sent it and who you sent it to; the message is data, and the other information is metadata.

The metadata we see in this block of code tells us what the key-value pairs from the last code block mean, and where the information stored there comes from. There's location data, a line telling us what about the format of the key-value pairs, some information about the equipment used to gather the data, and where the data came from.

### Identifiers

Identifiers¶

Finally, at the top, we have this:

{ 
    '_id': ObjectId('6126f1780e45360640bf240a')
}

This is the document's unique identifier, which is similar to the index label for each row in a pandas DataFrame.

<font size="+1">Practice</font>

Practice

Try it yourself! Retrieve a single document from the "nairobi" collection, and print the result.

## Analyzing Data

Analyzing Data¶

Now that we've seen what a document looks like in this collection, let's start working with what we've got. Since our metadata includes information about each sensor's "site", we might be curious to know how many sites are in the "lagos" collection. To do that, we'll use the distinct method, like this:

Notice that in order to grab the `"site"` number, we needed to include the `"metadata"` tag. 

Notice that in order to grab the "site" number, we needed to include the "metadata" tag.

This tells us that there are 2 sensor sites in Lagos: one labeled 3 and the other labeled 4.

Let's go further. We know that there are two sensor sites in Lagos, but we don't know how many documents are associated with each site. To find that out, we'll use the count_documents method for each site.

<font size="+1">Practice</font>

Practice

Try it yourself! Find out how many sensor sites are in Nairobi, what their labels are, and how many documents are associated with each one.

Now that we know how many *documents* are associated with each site, let's keep drilling down and find the number of *readings* for each site. We'll do this with the [`aggregate`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.aggregate) method.

Now that we know how many documents are associated with each site, let's keep drilling down and find the number of readings for each site. We'll do this with the aggregate method.

Before we run it, let's take a look at some of what's happening in the code here. First, you'll notice that there are several dollar signs ($) in the list. This is telling the collection that we want to create something new. Here, we're saying that we want there to be a new group, and that the new group needs to be updated with data from metadata.site, and then updated again with data from count.

There's also a new field: "_id". In PyMongo, "_id" is always the primary key. Primary keys are the fields which contain values that uniquely identify each row in a table.

Let's run the code and see what happens:

With that information in mind, we might want to know what those readings actually are. Since we're really interested in measures of air quality, let's take a look at the `P2` values in the `"lagos"` collection. `P2` measures the amount of particulate matter in the air, which in this case is something called PM 2.5. If we wanted to get all the documents in a collection, we could, but that would result in an unmanageably large number of records clogging up the memory on our machines. Instead, let's use the [`find`](https://pymongo.readthedocs.io/en/stable/api/pymongo/collection.html#pymongo.collection.Collection.find) method and use `limit` to make sure we only get back the first 3. 

With that information in mind, we might want to know what those readings actually are. Since we're really interested in measures of air quality, let's take a look at the P2 values in the "lagos" collection. P2 measures the amount of particulate matter in the air, which in this case is something called PM 2.5. If we wanted to get all the documents in a collection, we could, but that would result in an unmanageably large number of records clogging up the memory on our machines. Instead, let's use the find method and use limit to make sure we only get back the first 3.

<font size="+1">Practice</font>

Practice

Try it yourself! Find out how many sensor sites are in Nairobi, what their labels are, how many documents are associated with each one, and the number of observations from each site. Then, return the first three documents with the value P2.

So far, we've been dealing with relatively small subsets of the data in our collections, but what if we need to work with something bigger? Let's start by using `distinct` to remind ourselves of the kinds of data we have at our disposal.

So far, we've been dealing with relatively small subsets of the data in our collections, but what if we need to work with something bigger? Let's start by using distinct to remind ourselves of the kinds of data we have at our disposal.

There are also comparison query operators that can be helpful for filtering the data. In total, we have 

There are also comparison query operators that can be helpful for filtering the data. In total, we have

• $gt: greater than (>)
• $lt: less than (<)
• $gte: greater than equal to (>=)
• $lte: less than equal to (<= )

Let's use the timestamp to see how we can use these operators to select different documents:

<font size="+1">Practice</font>

Practice

Try it yourself! Find three documents with timestamp greater than or equal to and less than or equal the date December 12, 2018 — datetime.datetime(2018, 12, 1, 0, 0, 6, 767000).

## Updating Documents

Updating Documents¶

We can also update documents by passing some filter and new values using `update_one` to update one record or `update_many` to update many records. Let's look at an example. Before updating, we have this record showing like this:

We can also update documents by passing some filter and new values using update_one to update one record or update_many to update many records. Let's look at an example. Before updating, we have this record showing like this:

Now we are updating the sensor type from `"SDS011"` to `"SDS"`, we first select all records with sensor type equal to `"SDS011"`, then set the new value to `"SDS"`:

Now we are updating the sensor type from "SDS011" to "SDS", we first select all records with sensor type equal to "SDS011", then set the new value to "SDS":

Now we can see all records have changed:

Now we can see all records have changed:

We can change it back:

We can change it back:

## Aggregation

Aggregation¶

Since we're looking for *big* numbers, we need to figure out which one of those dimensions has the largest number of measurements by **aggregating** the data in each document. Since we already know that `site 3` has significantly more documents than `site 2`, let's start looking at `site 3`. We can use the `$match` syntax to only select `site 3` data. The code to do that looks like this: 

Since we're looking for big numbers, we need to figure out which one of those dimensions has the largest number of measurements by aggregating the data in each document. Since we already know that site 3 has significantly more documents than site 2, let's start looking at site 3. We can use the $match syntax to only select site 3 data. The code to do that looks like this:

<font size="+1">Practice</font>

Practice

Try it yourself! Find the number of each measurement type at site 29 in Nairobi.

After aggregation, there is another useful operator called `$project`, which allows you to specify which fields to display by adding new fields or deleting fields. Using the Nairobi data from site 29, we can first count each sensor type:<span style='color: transparent; font-size:1%'>WQU WorldQuant University Applied Data Science Lab QQQQ</span>

After aggregation, there is another useful operator called $project, which allows you to specify which fields to display by adding new fields or deleting fields. Using the Nairobi data from site 29, we can first count each sensor type:WQU WorldQuant University Applied Data Science Lab QQQQ

We can see there are two sensor types and the corresponding counts. If we only want to display what are the types but do not care about the counts, we can suppress the `count` filed by setting it at 0 in `$project`:

We can see there are two sensor types and the corresponding counts. If we only want to display what are the types but do not care about the counts, we can suppress the count filed by setting it at 0 in $project:

The `$project` syntax is also useful for deleting the intermediate fields that we used to generate our final fields but no longer need. In the following example, let's calculate the date difference for each sensor type. We'll first use the aggregation method to get the start date and last date. 

The $project syntax is also useful for deleting the intermediate fields that we used to generate our final fields but no longer need. In the following example, let's calculate the date difference for each sensor type. We'll first use the aggregation method to get the start date and last date.

Then we can calculate the date difference using `$dateDiff`, which gets the date difference through specifying the start date, end date and unit for timestamp data. We can see from the results above that the dates, are very close to each other. The only differences are in the minutes, so we can specify the unit as minute to show the difference. Since we don't need the start date and end dates, we can define a `"dateDiff"` field inside `$project`, so that it will be shown in the final display: 

Then we can calculate the date difference using $dateDiff, which gets the date difference through specifying the start date, end date and unit for timestamp data. We can see from the results above that the dates, are very close to each other. The only differences are in the minutes, so we can specify the unit as minute to show the difference. Since we don't need the start date and end dates, we can define a "dateDiff" field inside $project, so that it will be shown in the final display:

If we specify unit as `day`, it will show the difference between the dates:

If we specify unit as day, it will show the difference between the dates:

<font size="+1">Practice</font>

Practice

Try it yourself find the date difference for each measurement type at site 29 in Nairobi.

We can do more with the date data using `$dateTrunc`, which truncates datetime data. We need to specify the datetime data, which can be a `Date`, a `Timestamp`, or an `ObjectID`. Then we need to specify the `unit` (year, month, day, hour, minute, second) and `binSize` (numerical variable defining the size of the truncation). Let's check the example below, where we group data by the month using `$dateTrunc` and then count how many observations there are for each month.

We can do more with the date data using $dateTrunc, which truncates datetime data. We need to specify the datetime data, which can be a Date, a Timestamp, or an ObjectID. Then we need to specify the unit (year, month, day, hour, minute, second) and binSize (numerical variable defining the size of the truncation). Let's check the example below, where we group data by the month using $dateTrunc and then count how many observations there are for each month.

<font size="+1">Practice</font>

Practice

Try it yourself! Truncate date by week and count at site 29 in Nairobi.

## Finishing Up

Finishing Up¶

So far, we've connected to a server, accessed that server with a client, found the collection we were looking for within a database, and explored that collection in all sorts of different ways. Now it's time to get the data we'll actually need to build a model, and store that in a way we'll be able to use.

Let's use find to retrieve the PM 2.5 data from site 3. And, since we don't need any of the metadata to build our model, let's strip that out using the projection argument. In this case, we're telling the collection that we only want to see "timestamp" and "P2". Keep in mind that we limited the number of records we'll get back to 3 when we defined result above.

Finally, we'll use pandas to read the extracted data into a DataFrame, making sure to set `timestamp` as the index:

Finally, we'll use pandas to read the extracted data into a DataFrame, making sure to set timestamp as the index:

<font size="+1">Practice</font>

Practice

Try it yourself! Retrieve the PM 2.5 data from site 29 in Nairobi and strip out the metadata to create a DataFrame that shows only timestamp and P2. Print the result.

# References & Further Reading

References & Further Reading¶

• Further reading about servers and clients
• Definitions from the MongoDB documentation
• Information on Iterators
• MongoDB documentation in Aggregation

---

---

Copyright 2022 WorldQuant University. This content is licensed solely for personal use. Redistribution or publication of this material is strictly prohibited.

<font size="+3"><strong>Databases: SQL</strong></font>

Databases: SQL

# Working with SQL Databases

Working with SQL Databases¶

A database is a collection of interrelated data. The primary goal of a database is to store and retrieve information in a convenient and efficient way. There are many types of databases. In this section, we will be dealing with a **relational database**. A relational database is a widely used database model that consists of a collection of uniquely named **tables** used to store information. The structure of a database model with its tables, constraints, and relationships is called a **schema**. 

A database is a collection of interrelated data. The primary goal of a database is to store and retrieve information in a convenient and efficient way. There are many types of databases. In this section, we will be dealing with a relational database. A relational database is a widely used database model that consists of a collection of uniquely named tables used to store information. The structure of a database model with its tables, constraints, and relationships is called a schema.

A Structured Query Language (SQL), is used to retrieve information from a relational database. SQL is one of the most commonly used database languages. It allows data stored in a relational database to be queried, modified, and manipulated easily with basic commands. SQL powers database engines like MySQL, SQL Server, SQLite, and PostgreSQL. The examples and projects in this course will use SQLite.

A table refers to a collection of rows and columns in a relational database. When reading data into a pandas DataFrame, an index can be defined, which acts as the label for every row in the DataFrame.

# Connecting to a Database

Connecting to a Database¶

## ipython-sql 

ipython-sql¶

### Magic Commands

Magic Commands¶

Jupyter notebooks can run code that is not valid Python code but still affect the notebook . These special commands are called magic commands. Magic commands can have a range of properties. Some commonly used magic functions are below:

Jupyter notebooks can run code that is not valid Python code but still affect the notebook . These special commands are called magic commands. Magic commands can have a range of properties. Some commonly used magic functions are below:

Magic Command	Description of Command
%pwd	Print the current working directory
%cd	Change the current working directory
%ls	List the contents of the current directory
%history	Show the history of the In [ ]: commands

We will be leveraging magic commands to work with a SQLite database.

### ipython-sql

ipython-sql¶

`ipython-sql` allows you to write SQL code directly in a Jupyter Notebook. The `%sql` (or `%%sql`) magic command is added to the beginning of a code block and then SQL code can be written.

ipython-sql allows you to write SQL code directly in a Jupyter Notebook. The %sql (or %%sql) magic command is added to the beginning of a code block and then SQL code can be written.

### Connecting with ipython-sql

Connecting with ipython-sql¶

We can connect to a database using the %sql magic function:

We can connect to a database using the %sql magic function:

## sqlite3

sqlite3¶

We can also connect to the same database using the sqlite3 package:

We can also connect to the same database using the sqlite3 package:

# Querying a Database

Querying a Database¶

## Building Blocks of the Basic Query

Building Blocks of the Basic Query¶

There are six common clauses used for querying data:

There are six common clauses used for querying data:

Clause Name	Definition
SELECT	Determines which columns to include in the query's result
FROM	Identifies the table from which to query the data from
WHERE	filters data
GROUP BY	groups rows by common values in columns
HAVING	filters out unwanted groups from GROUP BY
ORDER BY	Orders the rows using one or more columns
LIMIT	Outputs the specified number of rows

All clauses may be used together, but SELECT and FROM are the only required clauses. The format of clauses is in the example query below:

SELECT column1, column2
FROM table_name
WHERE "conditions"
GROUP BY "column-list"
HAVING "conditions"
ORDER BY "column-list"

## SELECT and FROM

SELECT and FROM¶

You can use `SELECT *` to select all columns in a table. `FROM` specifies the table in the database to query. `LIMIT 5` will select only the first five rows. 

You can use SELECT * to select all columns in a table. FROM specifies the table in the database to query. LIMIT 5 will select only the first five rows.

Example

You can also use `SELECT` to select certain columns in a table

You can also use SELECT to select certain columns in a table

<font size="+1">Practice</font>

Practice

Try it yourself! Use SELECT to select the district_id column from the id_map table.

We can also assign an **alias** or temporary name to a column using the `AS` command. Aliases can also be used on a table. See the example below, which assigns the alias `household_number` to `household_id`

We can also assign an alias or temporary name to a column using the AS command. Aliases can also be used on a table. See the example below, which assigns the alias household_number to household_id

<font size="+1">Practice</font>

Practice

Try it yourself! Use SELECT, FROM, AS, and LIMIT to select the first 5 rows from the id_map table. Rename the district_id column to district_number.

## Filtering and Sorting Data

Filtering and Sorting Data¶

SQL provides a variety of comparison operators that can be used with the WHERE clause to filter the data. 

SQL provides a variety of comparison operators that can be used with the WHERE clause to filter the data.

Comparison Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

For example, to select the first 5 homes in Ramechhap (district `2`):

For example, to select the first 5 homes in Ramechhap (district 2):

<font size="+1">Practice</font>

Practice

Try it yourself! Use WHERE to select the row with household_id equal to 13735001

## Aggregating Data

Aggregating Data¶

Aggregation functions take a collection of values as inputs and return one value as the output. The table below gives the frequently used built-in aggregation functions:

Aggregation functions take a collection of values as inputs and return one value as the output. The table below gives the frequently used built-in aggregation functions:

Aggregation Function	Definition
MIN	Return the minimum value
MAX	Return the largest value
SUM	Return the sum of values
AVG	Return the average of values
COUNT	Return the number of observations

Use the `COUNT` function to find the number of observations in the `id_map` table that come from Ramechhap (district `2`):

Use the COUNT function to find the number of observations in the id_map table that come from Ramechhap (district 2):

Aggregation functions are frequently used with a `GROUP BY` clause to perform the aggregation on groups of data. For example, the query below returns the count of observations in each District:

Aggregation functions are frequently used with a GROUP BY clause to perform the aggregation on groups of data. For example, the query below returns the count of observations in each District:

 `DISTINCT` is a keyword to select unique records in a query result. For example, if we want to know the unique values in the `district_id` column:

DISTINCT is a keyword to select unique records in a query result. For example, if we want to know the unique values in the district_id column:

<font size="+1">Practice</font>

Practice

Try it yourself! Use DISTINCT to count the number of unique values in the vdcmun_id column.

`DISTINCT` and `COUNT` can be used in combination to count the number of distinct records. For example, if we want to know the number of unique values in the `district_id` column:

DISTINCT and COUNT can be used in combination to count the number of distinct records. For example, if we want to know the number of unique values in the district_id column:

<font size="+1">Practice</font>

Practice

Try it yourself! Use DISTINCT and COUNT to count the number of unique values in the vdcmun_id column.

# Joining Tables

Joining Tables¶

Joins link data from two or more tables together by using a column that is common between the two tables. The basic syntax for a join is below, where `table1` and `table2` refer to the two tables being joined, `column1` and `column2` refer to columns to be returned from both tables, and `ID` refers to the common column in the two tables. 

Joins link data from two or more tables together by using a column that is common between the two tables. The basic syntax for a join is below, where table1 and table2 refer to the two tables being joined, column1 and column2 refer to columns to be returned from both tables, and ID refers to the common column in the two tables.

SELECT table1.column1,
       table2.column2
FROM table_1
JOIN table2 ON table1.id = table1.id

We'll explore the concept of joins by first identifying a single household that we'd like to pull in building information for. For example, let's say we want to see the corresponding `foundation_type` for the first home in Ramechhap (District 1). We'll start by looking at this single record in the `id_map` table.

We'll explore the concept of joins by first identifying a single household that we'd like to pull in building information for. For example, let's say we want to see the corresponding foundation_type for the first home in Ramechhap (District 1). We'll start by looking at this single record in the id_map table.

This household has `building_id` equal to 23. Let's look at the `foundation_type` for this building, by filtering the `building_structure` table to find this building.

This household has building_id equal to 23. Let's look at the foundation_type for this building, by filtering the building_structure table to find this building.

To join the two tables and limit the results to `building_id = 23`:    

To join the two tables and limit the results to building_id = 23:

In addition to the basic `JOIN` clause, specific join types can be specified, which specify whether the common column needs to be in one, both, or either of the two tables being joined. The different join types are below. The left table is the table specified first, immediately after the `FROM` clause and the right table is the table specified after the `JOIN` clause. If the generic `JOIN` clause is used, then by default the `INNER JOIN` will be used.

In addition to the basic JOIN clause, specific join types can be specified, which specify whether the common column needs to be in one, both, or either of the two tables being joined. The different join types are below. The left table is the table specified first, immediately after the FROM clause and the right table is the table specified after the JOIN clause. If the generic JOIN clause is used, then by default the INNER JOIN will be used.

JOIN Type	Definition
INNER JOIN	Returns rows where ID is in both tables
LEFT JOIN	Returns rows where ID is in the left table. Return NA for values in column, if ID is not in right table.
RIGHT JOIN	Returns rows where ID is in the right table. Return NA for values in column, if ID is not in left table.
FULL JOIN	Returns rows where ID is in either table. Return NA for values in column, if ID is not in either table.
WQU WorldQuant University Applied Data Science Lab QQQQ

The video below outlines the main types of joins:

The video below outlines the main types of joins:

<font size="+1">Practice</font>

Practice

Try it yourself! Use the DISTINCT command to create a column with all unique building IDs in the id_map table. LEFT JOIN this column with the roof_type column from the building_structure table, showing only buildings where district_id is 1 and limiting your results to the first five rows of the new table.

# Using pandas with SQL Databases

Using pandas with SQL Databases¶

To save the output of a query into a pandas DataFrame, we will use connect to the SQLite database using the SQLite3 package:

To save the output of a query into a pandas DataFrame, we will use connect to the SQLite database using the SQLite3 package:

To run a query using `sqlite3`, we need to store the query as a string. For example, the variable below called `query` is a string containing a query which returns the first 10 rows from the `id_map` table:

To run a query using sqlite3, we need to store the query as a string. For example, the variable below called query is a string containing a query which returns the first 10 rows from the id_map table:

To save the results of the query into a pandas DataFrame, use the `pd.read_sql()` function. The optional parameter `index_col` can be used to set the index to a specific column from the query. 

To save the results of the query into a pandas DataFrame, use the pd.read_sql() function. The optional parameter index_col can be used to set the index to a specific column from the query.

<font size="+1">Practice</font>

Practice

Try it yourself! Use the pd.read_sql function to save the results of a query to a DataFrame. The query should select first 20 rows from the id_map table.

# References & Further Reading

References & Further Reading¶

• Additional Explanation of Magic Commands
• ipython-SQL User Documentation
• Data Carpentry Course on SQL in Python
• SQL Course Material on GitHub (1)
• SQL Course Material on GitHub (2)

---

---

Copyright 2022 WorldQuant University. This content is licensed solely for personal use. Redistribution or publication of this material is strictly prohibited.

xxxxxxxxxx

<font size="+3"><strong>Machine Learning: Data Pre-Processing and Production</strong></font>

Machine Learning: Data Pre-Processing and Production

xxxxxxxxxx

# What's scikit-learn?

What's scikit-learn?¶

scikit-learn is a Python library that contains implementations of many common machine learning algorithms and uses common interfaces for these that enables experimentation. In this section, we'll look at linear regression (which you'll use to predict price based on the area of a property) and K-nearest neighbors, which you'll use to classify the neighborhood a property is in.

xxxxxxxxxx

# Data Preprocessing

Data Preprocessing¶

xxxxxxxxxx

# Standardization

Standardization¶

xxxxxxxxxx

**Standardization** is a widely used scaling technique to transform features before fitting into models. Feature scaling changes all a dataset's continuous features to give us a more consistent range of values. Specifically, we subtract the mean from each data point and then divide by the standard deviation:

Standardization is a widely used scaling technique to transform features before fitting into models. Feature scaling changes all a dataset's continuous features to give us a more consistent range of values. Specifically, we subtract the mean from each data point and then divide by the standard deviation:

$$\hat{X} = \frac{X-\mu}{\sigma},$$

The goal of standardization is to improve model performance having all continuous features be on the same scale. It's useful in at least two circumstances:

1. For machine leaning algorithms that use Euclidean distance (k-means and k-nearest neighbors), different scales can distort the calculation of distance and hurt model performance.
2. For dimensionality reduction (principal component analysis), it can improve the model's ability to finds combinations of features that have the most variance.

xxxxxxxxxx

Let's check the following example where we apply standardization on one of the columns in the following DataFrame:

Let's check the following example where we apply standardization on one of the columns in the following DataFrame:

xxxxxxxxxx

Our target feature is the `"surface_covered_in_m2"` column. Let's first check the maximum and minimum of this column before standardization:

Our target feature is the "surface_covered_in_m2" column. Let's first check the maximum and minimum of this column before standardization:

xxxxxxxxxx

We can perform the transformation by first instantiating the scaler and assigning the feature to a variable name. Then we fit the scaler and transform the data:

We can perform the transformation by first instantiating the scaler and assigning the feature to a variable name. Then we fit the scaler and transform the data:

xxxxxxxxxx

Now you can see the transformed data range is much smaller after standardization:

Now you can see the transformed data range is much smaller after standardization:

xxxxxxxxxx

We can also combine the fit and transform process together into one step:

We can also combine the fit and transform process together into one step:

xxxxxxxxxx

<font size="+1">Practice</font>  

Practice

Standardize the price column in "mexico-city-real-estate-1.csv":

xxxxxxxxxx

## One-Hot Encoding

One-Hot Encoding¶

xxxxxxxxxx

A property's district is **categorical data**, or data which can be divided into groups.  For many machine learning algorithms, it's common to create a column in a DataFrame to indicate if the feature is present or absent, instead of using the category's name. First you a column for each district names then, for each observation, you put a 1 or a 0 to indicate if the property is located in each neighborhood or not. Let's take a look at the `mexico-city-test-features.csv` dataset for properties which include the district.

A property's district is categorical data, or data which can be divided into groups. For many machine learning algorithms, it's common to create a column in a DataFrame to indicate if the feature is present or absent, instead of using the category's name. First you a column for each district names then, for each observation, you put a 1 or a 0 to indicate if the property is located in each neighborhood or not. Let's take a look at the mexico-city-test-features.csv dataset for properties which include the district.

xxxxxxxxxx

You can do one-hot encoding using pandas [`get_dummies`](https://pandas.pydata.org/docs/reference/api/pandas.get_dummies.html) function, but we'll use a the [Category Encoders](https://contrib.scikit-learn.org/category_encoders/) library since it allows us to integrate the one hot encoder as a transformer in a scikit-learn Pipeline.

You can do one-hot encoding using pandas get_dummies function, but we'll use a the Category Encoders library since it allows us to integrate the one hot encoder as a transformer in a scikit-learn Pipeline.

xxxxxxxxxx

<font size="+1">Practice</font>  

Practice

Create a DataFrame which one-hot encodes the property_type column in mexico-city-real-estate-1.csv. The DataFrame you create should have extra columns for apartments, houses, and stores.

xxxxxxxxxx

## Ordinal Encoding

Ordinal Encoding¶